﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_update_sqlserver]
#-- Purpose:		Updates the server discovery record with the latest 
#--					information
#--	Last Update:	07/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_update_sqlserver]
(
	@server_name		varchar(60),
	@instance_name		varchar(60)		= NULL,
	@engine_edition		int				= NULL,
	@edition_id			int				= NULL,
	@product_version	varchar(25)		= NULL,
	@product_level		varchar(5)		= NULL,
	@has_master_jobs	bit				= NULL,
	@has_local_jobs		bit				= NULL,
	@backup_directory	varchar(255)	= NULL,
	@is_mixed_mode		bit				= NULL,
	@has_centraldba_db	bit				= NULL
)
AS

--- Declare Local Variables
DECLARE @id INT

--- Parameter Preprocessing
IF LTRIM(RTRIM(@product_version)) = ''
	SET @product_version = NULL
IF LTRIM(RTRIM(@product_level)) = ''
	SET @product_level = NULL
IF LTRIM(RTRIM(@backup_directory)) = ''
	SET @backup_directory = NULL

--- Get the Server ID
SELECT		@id = ssd_id
FROM		[dbo].[tblMSX_server_discovery] (NOLOCK)
WHERE		ssd_server_name = @server_name
			AND ISNULL(ssd_instance_name, '') = ISNULL(@instance_name, '')


--- Was this server previously discovered?
IF @id IS NOT NULL
  BEGIN		
	--- SQL Server Record data has changed; update the data
	IF NOT EXISTS (
				SELECT		TOP 1 * 
				FROM		[dbo].[tblMSX_server_sql_info] (NOLOCK)
				WHERE		sql_ssd_id = @id
				)
	  BEGIN
		IF @engine_edition IS NOT NULL OR @edition_id IS NOT NULL OR @product_version IS NOT NULL
		  BEGIN
			INSERT INTO [dbo].[tblMSX_server_sql_info]
				(
					sql_ssd_id,
					sql_last_update,
					sql_centraldba_db_added,
					sql_master_jobs_added,
					sql_engine_edition,
					sql_edition_id,
					sql_product_version,
					sql_product_level,
					sql_has_master_jobs,
					sql_has_local_jobs,
					sql_backup_directory,
					sql_is_mixed_mode,
					sql_has_centraldba_db
				)
			VALUES
				(
					@id,
					GETDATE(),
					(CASE WHEN @has_centraldba_db = 1 THEN GETDATE() ELSE NULL END),
					(CASE WHEN @has_master_jobs = 1 THEN GETDATE() ELSE NULL END),
					@engine_edition,
					@edition_id,
					@product_version,
					@product_level,
					@has_master_jobs,
					@has_local_jobs,
					@backup_directory,
					@is_mixed_mode,
					@has_centraldba_db
				)
		  END
	  END
	ELSE IF EXISTS (
		SELECT		TOP 1 * 
		FROM		[dbo].[tblMSX_server_sql_info] (NOLOCK)
		WHERE		sql_ssd_id = @id
					AND (
						ISNULL(sql_engine_edition, -999) <> ISNULL(@engine_edition, -999)
						OR ISNULL(sql_edition_id, -999) <> ISNULL(@edition_id, -999)
						OR ISNULL(sql_product_version, '') <> ISNULL(@product_version, '')
						OR ISNULL(sql_product_level, '') <> ISNULL(@product_level, '')
						OR ISNULL(CAST(sql_has_master_jobs as int), -999) <> ISNULL(CAST(@has_master_jobs as int), -999)
						OR ISNULL(CAST(sql_has_local_jobs as int), -999) <> ISNULL(CAST(@has_local_jobs as int), -999)
						OR ISNULL(sql_backup_directory, '') <> ISNULL(@backup_directory, '')
						OR ISNULL(CAST(sql_is_mixed_mode as int), -999) <> ISNULL(CAST(@is_mixed_mode as int), -999)
						OR ISNULL(CAST(sql_has_centraldba_db as int), -999) <> ISNULL(CAST(@has_centraldba_db as int), -999)
					)
		)
	  BEGIN
		UPDATE		[dbo].[tblMSX_server_sql_info]
		SET			sql_engine_edition = @engine_edition,
					sql_edition_id = @edition_id,
					sql_product_version = @product_version,
					sql_product_level = @product_level,
					sql_has_master_jobs = @has_master_jobs,
					sql_has_local_jobs = @has_local_jobs,
					sql_backup_directory = @backup_directory,
					sql_is_mixed_mode = @is_mixed_mode,
					sql_has_centraldba_db = @has_centraldba_db,
					sql_last_update = GETDATE(),
					sql_centraldba_db_added = (CASE WHEN sql_centraldba_db_added IS NULL AND ISNULL(@has_centraldba_db, 0) = 1 THEN GETDATE() ELSE sql_centraldba_db_added END),
					sql_master_jobs_added = (CASE WHEN sql_master_jobs_added IS NULL AND ISNULL(@has_master_jobs, 0) = 1 THEN GETDATE() ELSE sql_master_jobs_added END)
		WHERE		sql_ssd_id = @id
					AND (
						ISNULL(sql_engine_edition, -999) <> ISNULL(@engine_edition, -999)
						OR ISNULL(sql_edition_id, -999) <> ISNULL(@edition_id, -999)
						OR ISNULL(sql_product_version, '') <> ISNULL(@product_version, '')
						OR ISNULL(sql_product_level, '') <> ISNULL(@product_level, '')
						OR ISNULL(CAST(sql_has_master_jobs as int), -999) <> ISNULL(CAST(@has_master_jobs as int), -999)
						OR ISNULL(CAST(sql_has_local_jobs as int), -999) <> ISNULL(CAST(@has_local_jobs as int), -999)
						OR ISNULL(sql_backup_directory, '') <> ISNULL(@backup_directory, '')
						OR ISNULL(CAST(sql_is_mixed_mode as int), -999) <> ISNULL(CAST(@is_mixed_mode as int), -999)
						OR ISNULL(CAST(sql_has_centraldba_db as int), -999) <> ISNULL(CAST(@has_centraldba_db as int), -999)
					)
	  END

	--- Just mark that the SQL Server info was retrieved still
	ELSE  
	  BEGIN
		UPDATE		[dbo].[tblMSX_server_sql_info]
		SET			sql_last_update = GETDATE()
		WHERE		sql_ssd_id = @id
	  END
  END
